package com.bsoft.gol.hcs.specialist.dao;

/**
 * @Author:FangZhiyi
 * @Description
 * @Data Created in 2020/7/15 9:49
 * @Moddified By:
 */

import com.bsoft.gol.hcs.enumcase.FollowTypeEnum;
import com.google.common.collect.Lists;
import ctd.persistence.annotation.DAOMethod;
import ctd.persistence.annotation.DAOParam;
import ctd.persistence.support.hibernate.template.AbstractHibernateStatelessResultAction;
import ctd.persistence.support.hibernate.template.HibernateSessionTemplate;
import ctd.persistence.support.hibernate.template.HibernateStatelessResultAction;
import ctd.spring.boot.annotation.SsdevDao;
import ctd.util.annotation.RpcService;
import ctd.util.converter.ConversionUtils;
import org.apache.commons.lang3.StringUtils;
import org.atag.dao.ext.HibernateExtendDAO;
import org.hibernate.Query;
import org.hibernate.StatelessSession;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.transform.Transformers;
import com.bsoft.gol.hcs.specialist.bean.request.*;
import com.bsoft.gol.hcs.specialist.bean.response.*;
import com.bsoft.gol.hcs.specialist.bean.vo.FollowUpRecordsVO;
import com.bsoft.gol.hcs.specialist.entity.FollowUpRecords;
import com.bsoft.gol.hcs.specialist.entity.FollowUpRecordsContent;
import com.bsoft.gol.hcs.utils.CommonUtils;

import java.math.BigInteger;
import java.util.*;
@SsdevDao( serviceId = "followUpRecordsDAO")
public abstract class FollowUpRecordsDAO extends HibernateExtendDAO<FollowUpRecords> {
    public FollowUpRecordsDAO() {
        this.setKeyField("recordId");
    }


    @DAOMethod(sql = " from FollowUpRecords where userInfoId =:userInfoId")
    public abstract FollowUpRecords getFollowUpRecordsByUserInfoId(@DAOParam("userInfoId") String userInfoId);

    /**
     * 查询该病人所有未执行的计划
     */
    @DAOMethod(sql = " from FollowUpRecords where patientId =:patientId AND organizationId=:organizationId AND followUpStatus in (0, 2)")
    public abstract List<FollowUpRecords> findPatientAllPlan(@DAOParam("patientId") String patientId, @DAOParam("organizationId") String organizationId);

    /**
     * 查询医生当天未随访计划数量
     * @param docId
     * @param startDate
     * @param endDate
     * @return
     */
    @RpcService
    @DAOMethod(sql = " select count(1) from FollowUpRecords where docId=:docId and planTime >=:startDate and planTime >=:endDate and followUpStatus='1' ")
    public abstract long getFollowUpRecordsCount(@DAOParam("docId")String docId,@DAOParam("startDate")Date startDate,@DAOParam("endDate")Date endDate);
    /**
     * 专病随访审核改变审核状态
     *
     * @param auditStatus the auditStatus
     * @param recordIdList     the recordIdList
     * @param auditName   the auditName
     * @author Qulc - 2022/6/6 17:05
     */
    @DAOMethod(sql = "update FollowUpRecords set auditStatus=:auditStatus,auditName=:auditName where recordId in (:recordIdList)")
    public abstract void updateAuditStatus(@DAOParam("auditStatus") Byte auditStatus,@DAOParam("recordIdList") List<Long> recordIdList,@DAOParam("auditName") String auditName);
    /**
     * @param request
     * @throws
     * @description: 获取随访计划记录列表
     * @return: java.util.List<specialist.entity.FollowUpRecords>
     * @author: tongtp
     * @date: 2020/7/23 9:51
     */
    public List<FollowUpRecordsVO> queryExecutionList(final QueryExecutionListRequest request) {
        HibernateStatelessResultAction<List<FollowUpRecordsVO>> action =
                new AbstractHibernateStatelessResultAction<List<FollowUpRecordsVO>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append(" select record_id as recordId,organization_id as organizationId," +
                                "route_detail_type as routeDetailType,disease_type_id as diseaseTypeId," +
                                "disease_type_name as diseaseTypeName,department_id as departmentId,department_name " +
                                "as departmentName,doctor_id as doctorId,doctor_name as doctorName,patient_id as " +
                                "patientId,card_type as cardType,card_number as cardNumber,patient_name as " +
                                "patientName,patient_sex as patientSex,patient_age as patientAge,telephone as " +
                                "telephone,address as address," +
                                "plan_time as planTime,result_code as resultCode,follow_up_status as followUpStatus," +
                                "create_id as createId,create_time as createTime,execute_id as executeId," +
                                "execute_name as executeName,execute_time" +
                                " as executeTime,stop_id as stopId,stop_time as stopTime,route_id as routeId," +
                                "route_name as routeName,route_detail_id as routeDetailId,user_info_id as " +
                                "userInfoId,gmt_create as gmtCreate,gmt_modified as gmtModified,is_notify as " +
                                "isNotify,is_read as isRead,plan_notify_date as planNotifyDate,notify_status as notifyStatus," +
                                "medical_record_id as medicalRecordId,medical_record_name as medicalRecordName," +
                                "patient_medical_card_type as patientMedicalCardType," +
                                "patient_medical_card_number as patientMedicalCardNumber,team_chronic_group_id as " +
                                "teamChronicGroupId,medical_team_id as medicalTeamId,medical_team_name as " +
                                "medicalTeamName,answer_abnormal as answerAbnormal,medical_record_upload_flag as medicalRecordUploadFlag " +
                                "from  follow_up_records where 1=1 ");
                        if (CommonUtils.isNotEmpty(request.getOrganizationId())) {
                            sqlBuilder.append(" and organization_id = :organizationId ");
                        }
                        if (CommonUtils.isNotEmpty(request.getDiseaseTypeId())) {
                            sqlBuilder.append(" and disease_type_id = :diseaseTypeId ");
                        }

                        if (CommonUtils.isNotEmpty(request.getRouteDetailId())) {
                            sqlBuilder.append(" and route_detail_id = :routeDetailId ");
                        }
                        if (CommonUtils.isNotEmpty(request.getRouteId())) {
                            sqlBuilder.append(" and route_id = :routeId ");
                        }
                        //doctor_id 新增医生id查询条件09-16
                        if (CommonUtils.isNotEmpty(request.getDoctorId())) {
                            sqlBuilder.append(" and doctor_id = :doctorId ");
                        }
                        if (CommonUtils.isNotEmpty(request.getDocId())) {
                            sqlBuilder.append(" and doc_id = :docId ");
                        }

                        if (CommonUtils.isNotEmpty(request.getRouteDetailType())) {
                            sqlBuilder.append(" and route_detail_type = :routeDetailType ");
                        }
                        if (CommonUtils.isNotEmpty(request.getPatientName())) {
                            sqlBuilder.append(" and (patient_name like :patientName or patient_medical_card_number " +
                                    "like" +
                                    " " +
                                    ":patientName) ");
                        }
                        if (CommonUtils.isNotEmpty(request.getStartDate())) {
                            sqlBuilder.append(" and plan_time >= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(request.getEndDate())) {
                            sqlBuilder.append(" and plan_time <= :endDate ");
                        } else {
                            if (CommonUtils.isNotEmpty(request.getStartDate())) {
                                sqlBuilder.append(" and plan_time <= :endDate ");
                            }
                        }
                        if (CommonUtils.isNotEmpty(request.getDepartmentId())) {
                            sqlBuilder.append(" and department_id = :departmentId ");
                        }
                        if (CommonUtils.isNotEmpty(request.getResultCode())) {
                            sqlBuilder.append(" and result_code = :resultCode ");
                        }
                        if (request.getFollowUpStatus() != null) {
                            sqlBuilder.append(" and follow_up_status = :followUpStatus ");
                        }
                        if (StringUtils.isNotEmpty(request.getAnswerAbnormal())) {
                            sqlBuilder.append(" and answer_abnormal = :answerAbnormal and abnormal_read_flag='0' ");
                        }
                        if (CommonUtils.isNotEmpty(request.getOrderBy())) {
                            sqlBuilder.append("  order by plan_time desc ");
                        } else {
                            sqlBuilder.append("  order by plan_time desc ");
                        }

                        Query query = ss.createSQLQuery(sqlBuilder.toString());
                        if (CommonUtils.isNotEmpty(request.getOrganizationId())) {
                            query.setParameter("organizationId", request.getOrganizationId());
                        }
                        if (CommonUtils.isNotEmpty(request.getDiseaseTypeId())) {
                            query.setParameter("diseaseTypeId", request.getDiseaseTypeId());
                        }

                        if (CommonUtils.isNotEmpty(request.getRouteDetailId())) {
                            query.setParameter("routeDetailId", request.getRouteDetailId());
                        }
                        if (CommonUtils.isNotEmpty(request.getRouteId())) {
                            query.setParameter("routeId", request.getRouteId());
                        }
                        if (CommonUtils.isNotEmpty(request.getDoctorId())) {
                            query.setParameter("doctorId", request.getDoctorId());
                        }
                        if (CommonUtils.isNotEmpty(request.getDocId())) {
                            query.setParameter("docId", request.getDocId());
                        }
                        if (CommonUtils.isNotEmpty(request.getRouteDetailType())) {
                            query.setParameter("routeDetailType", request.getRouteDetailType());
                        }
                        if (CommonUtils.isNotEmpty(request.getPatientName())) {
                            query.setParameter("patientName", request.getPatientName() + "%");
                        }
                        if (CommonUtils.isNotEmpty(request.getStartDate())) {
                            query.setParameter("startDate", request.getStartDate());
                        }
                        if (CommonUtils.isNotEmpty(request.getEndDate())) {
                            query.setParameter("endDate", request.getEndDate());
                        } else {
                            if (CommonUtils.isNotEmpty(request.getStartDate())) {
                                query.setParameter("endDate", request.getStartDate());
                            }
                        }
                        if (CommonUtils.isNotEmpty(request.getDepartmentId())) {
                            query.setParameter("departmentId", request.getDepartmentId());
                        }
                        if (CommonUtils.isNotEmpty(request.getResultCode())) {
                            query.setParameter("resultCode", request.getResultCode());
                        }
                        if (request.getFollowUpStatus() != null) {
                            query.setParameter("followUpStatus", request.getFollowUpStatus());
                        }
                        if (StringUtils.isNotEmpty(request.getAnswerAbnormal())) {
                            query.setParameter("answerAbnormal", request.getAnswerAbnormal());
                        }
                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                        //分页
                        Integer pageSize = request.getPageSize();

                        //没有传pagesize 或者pagesize 为-1时不分页
                        if(Objects.nonNull(pageSize) && pageSize != -1){
                            int first = request.getPageNo() > 0 ? (request.getPageNo() - 1) * pageSize : 0;
                            query.setFirstResult(first);
                            query.setMaxResults(pageSize);
                        }
                        List<Map> list = query.list();
                        List<FollowUpRecordsVO> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, FollowUpRecordsVO.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * @param
     * @return java.util.List<specialist.bean.vo.FollowUpRecordsVO>
     * @@description: 查询随访计划中不含本医生的所有随访计划
     * @author zangsf
     * @date 2021-12-16 14:46
     */
    public List<FollowUpRecordsVO> queryExecutionListByTeamId(final QueryExecutionListRequest request) {
        HibernateStatelessResultAction<List<FollowUpRecordsVO>> action =
                new AbstractHibernateStatelessResultAction<List<FollowUpRecordsVO>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append(" select record_id as recordId,organization_id as organizationId," +
                                "route_detail_type as routeDetailType,disease_type_id as diseaseTypeId," +
                                "disease_type_name as diseaseTypeName,department_id as departmentId,department_name " +
                                "as departmentName,doctor_id as doctorId,doctor_name as doctorName,patient_id as " +
                                "patientId,card_type as cardType,card_number as cardNumber,patient_name as " +
                                "patientName,patient_sex as patientSex,patient_age as patientAge,telephone as " +
                                "telephone,address as address," +
                                "plan_time as planTime,result_code as resultCode,follow_up_status as followUpStatus," +
                                "create_id as createId,create_time as createTime,execute_id as executeId," +
                                "execute_name as executeName,execute_time" +
                                " as executeTime,stop_id as stopId,stop_time as stopTime,route_id as routeId," +
                                "route_name as routeName,route_detail_id as routeDetailId,user_info_id as " +
                                "userInfoId,gmt_create as gmtCreate,gmt_modified as gmtModified,is_notify as " +
                                "isNotify,is_read as isRead,plan_notify_date as planNotifyDate,notify_status as notifyStatus," +
                                "medical_record_id as medicalRecordId,medical_record_name as medicalRecordName," +
                                "patient_medical_card_type as patientMedicalCardType," +
                                "patient_medical_card_number as patientMedicalCardNumber,team_chronic_group_id as " +
                                "teamChronicGroupId,medical_team_id as medicalTeamId,medical_team_name as " +
                                "medicalTeamName,answer_abnormal as answerAbnormal,medical_record_upload_flag as medicalRecordUploadFlag " +
                                "from  follow_up_records where 1=1 ");
                        if (CommonUtils.isNotEmpty(request.getOrganizationId())) {
                            sqlBuilder.append(" and organization_id = :organizationId ");
                        }
                        if (CommonUtils.isNotEmpty(request.getDiseaseTypeId())) {
                            sqlBuilder.append(" and disease_type_id = :diseaseTypeId ");
                        }

                        if (CommonUtils.isNotEmpty(request.getRouteDetailId())) {
                            sqlBuilder.append(" and route_detail_id = :routeDetailId ");
                        }
                        if (CommonUtils.isNotEmpty(request.getRouteId())) {
                            sqlBuilder.append(" and route_id = :routeId ");
                        }
                        //doctor_id 新增医生id查询条件09-16
                        if (CommonUtils.isNotEmpty(request.getDoctorId())) {
                            sqlBuilder.append(" and doctor_id = :doctorId ");
                        }
                        if (CommonUtils.isNotEmpty(request.getTeamChronicDiseaseIdList())){
                            sqlBuilder.append(" and disease_type_id in:teamChronicDiseaseIdList ");
                        }
                        if (CommonUtils.isNotEmpty(request.getDocId())) {
                            sqlBuilder.append(" and doc_id != :docId ");
                        }

                        if (CommonUtils.isNotEmpty(request.getRouteDetailType())) {
                            sqlBuilder.append(" and route_detail_type = :routeDetailType ");
                        }
                        if (CommonUtils.isNotEmpty(request.getPatientName())) {
                            sqlBuilder.append(" and (patient_name like :patientName or patient_medical_card_number " +
                                    "like" +
                                    " " +
                                    ":patientName) ");
                        }
                        if (CommonUtils.isNotEmpty(request.getStartDate())) {
                            sqlBuilder.append(" and plan_time >= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(request.getEndDate())) {
                            sqlBuilder.append(" and plan_time <= :endDate ");
                        } else {
                            if (CommonUtils.isNotEmpty(request.getStartDate())) {
                                sqlBuilder.append(" and plan_time <= :endDate ");
                            }
                        }
                        if (CommonUtils.isNotEmpty(request.getDepartmentId())) {
                            sqlBuilder.append(" and department_id = :departmentId ");
                        }
                        if (CommonUtils.isNotEmpty(request.getResultCode())) {
                            sqlBuilder.append(" and result_code = :resultCode ");
                        }
                        if (request.getFollowUpStatus() != null) {
                            sqlBuilder.append(" and follow_up_status = :followUpStatus ");
                        }
                        if (StringUtils.isNotEmpty(request.getAnswerAbnormal())) {
                            sqlBuilder.append(" and answer_abnormal = :answerAbnormal and abnormal_read_flag='0' ");
                        }
                        if (CommonUtils.isNotEmpty(request.getOrderBy())) {
                            sqlBuilder.append("  order by plan_time desc ");
                        } else {
                            sqlBuilder.append("  order by plan_time desc ");
                        }

                        Query query = ss.createSQLQuery(sqlBuilder.toString());
                        if (CommonUtils.isNotEmpty(request.getOrganizationId())) {
                            query.setParameter("organizationId", request.getOrganizationId());
                        }
                        if (CommonUtils.isNotEmpty(request.getDiseaseTypeId())) {
                            query.setParameter("diseaseTypeId", request.getDiseaseTypeId());
                        }

                        if (CommonUtils.isNotEmpty(request.getRouteDetailId())) {
                            query.setParameter("routeDetailId", request.getRouteDetailId());
                        }
                        if (CommonUtils.isNotEmpty(request.getRouteId())) {
                            query.setParameter("routeId", request.getRouteId());
                        }
                        if (CommonUtils.isNotEmpty(request.getDoctorId())) {
                            query.setParameter("doctorId", request.getDoctorId());
                        }
                        if (CommonUtils.isNotEmpty(request.getTeamChronicDiseaseIdList())){
                            query.setParameterList("teamChronicDiseaseIdList", request.getTeamChronicDiseaseIdList());
                        }
                        if (CommonUtils.isNotEmpty(request.getDocId())) {
                            query.setParameter("docId", request.getDocId());
                        }
                        if (CommonUtils.isNotEmpty(request.getRouteDetailType())) {
                            query.setParameter("routeDetailType", request.getRouteDetailType());
                        }
                        if (CommonUtils.isNotEmpty(request.getPatientName())) {
                            query.setParameter("patientName", "%"+request.getPatientName() + "%");
                        }
                        if (CommonUtils.isNotEmpty(request.getStartDate())) {
                            query.setParameter("startDate", request.getStartDate());
                        }
                        if (CommonUtils.isNotEmpty(request.getEndDate())) {
                            query.setParameter("endDate", request.getEndDate());
                        } else {
                            if (CommonUtils.isNotEmpty(request.getStartDate())) {
                                query.setParameter("endDate", request.getStartDate());
                            }
                        }
                        if (CommonUtils.isNotEmpty(request.getDepartmentId())) {
                            query.setParameter("departmentId", request.getDepartmentId());
                        }
                        if (CommonUtils.isNotEmpty(request.getResultCode())) {
                            query.setParameter("resultCode", request.getResultCode());
                        }
                        if (request.getFollowUpStatus() != null) {
                            query.setParameter("followUpStatus", request.getFollowUpStatus());
                        }
                        if (StringUtils.isNotEmpty(request.getAnswerAbnormal())) {
                            query.setParameter("answerAbnormal", request.getAnswerAbnormal());
                        }
                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
                        //分页
                        int first = request.getPageNo() > 0 ? (request.getPageNo() - 1) * request.getPageSize() : 0;
                        query.setFirstResult(first);
                        query.setMaxResults(request.getPageSize());

                        List<Map> list = query.list();
                        List<FollowUpRecordsVO> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, FollowUpRecordsVO.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * @param request
     * @throws
     * @description: 获取随访计划记录总记录数
     * @return: java.lang.Long
     * @author: tongtp
     * @date: 2020/7/23 9:51
     */
    public Long queryExecutionListCount(final QueryExecutionListRequest request) {
        HibernateStatelessResultAction<Long> action = new AbstractHibernateStatelessResultAction<Long>() {
            @Override
            public void execute(StatelessSession ss) {
                StringBuilder sqlBuilder = new StringBuilder();
                sqlBuilder.append(" select count(1) countValue from follow_up_records where 1=1 ");
                if (CommonUtils.isNotEmpty(request.getOrganizationId())) {
                    sqlBuilder.append(" and organization_id = :organizationId ");
                }
                if (CommonUtils.isNotEmpty(request.getDiseaseTypeId())) {
                    sqlBuilder.append(" and disease_type_id = :diseaseTypeId ");
                }
                if (CommonUtils.isNotEmpty(request.getRouteDetailId())) {
                    sqlBuilder.append(" and route_detail_id = :routeDetailId ");
                }
                if (CommonUtils.isNotEmpty(request.getRouteId())) {
                    sqlBuilder.append(" and route_id = :routeId ");
                }
                if (CommonUtils.isNotEmpty(request.getDoctorId())) {
                    sqlBuilder.append(" and doctor_id = :doctorId ");
                }
                if (CommonUtils.isNotEmpty(request.getDocId())) {
                    sqlBuilder.append(" and doc_id = :docId ");
                }
                if (CommonUtils.isNotEmpty(request.getRouteDetailType())) {
                    sqlBuilder.append(" and route_detail_type = :routeDetailType ");
                }
                if (CommonUtils.isNotEmpty(request.getPatientName())) {
                    sqlBuilder.append(" and (patient_name like :patientName or patient_medical_card_number like" +
                            " " +
                            ":patientName) ");
                }
                if (CommonUtils.isNotEmpty(request.getStartDate())) {
                    sqlBuilder.append(" and plan_time >= :startDate ");
                }
                if (CommonUtils.isNotEmpty(request.getEndDate())) {
                    sqlBuilder.append(" and plan_time <= :endDate ");
                } else {
                    if (CommonUtils.isNotEmpty(request.getStartDate())) {
                        sqlBuilder.append(" and plan_time <= :endDate ");
                    }
                }
                if (CommonUtils.isNotEmpty(request.getDepartmentId())) {
                    sqlBuilder.append(" and department_id = :departmentId ");
                }
                if (CommonUtils.isNotEmpty(request.getResultCode())) {
                    sqlBuilder.append(" and result_code = :resultCode ");
                }
                if (request.getFollowUpStatus() != null) {
                    sqlBuilder.append(" and follow_up_status = :followUpStatus ");
                }
                if (StringUtils.isNotEmpty(request.getAnswerAbnormal())) {
                    sqlBuilder.append(" and answer_abnormal = :answerAbnormal and abnormal_read_flag='0' ");
                }

                Query query = ss.createSQLQuery(sqlBuilder.toString());
                if (CommonUtils.isNotEmpty(request.getOrganizationId())) {
                    query.setParameter("organizationId", request.getOrganizationId());
                }
                if (CommonUtils.isNotEmpty(request.getDiseaseTypeId())) {
                    query.setParameter("diseaseTypeId", request.getDiseaseTypeId());
                }
                if (CommonUtils.isNotEmpty(request.getRouteDetailId())) {
                    query.setParameter("routeDetailId", request.getRouteDetailId());
                }
                if (CommonUtils.isNotEmpty(request.getRouteId())) {
                    query.setParameter("routeId", request.getRouteId());
                }
                if (CommonUtils.isNotEmpty(request.getDoctorId())) {
                    query.setParameter("doctorId", request.getDoctorId());
                }
                if (CommonUtils.isNotEmpty(request.getDocId())) {
                    query.setParameter("docId", request.getDocId());
                }
                if (CommonUtils.isNotEmpty(request.getRouteDetailType())) {
                    query.setParameter("routeDetailType", request.getRouteDetailType());
                }
                if (CommonUtils.isNotEmpty(request.getPatientName())) {
                    query.setParameter("patientName", request.getPatientName() + "%");
                }
                if (CommonUtils.isNotEmpty(request.getStartDate())) {
                    query.setParameter("startDate", request.getStartDate());
                }
                if (CommonUtils.isNotEmpty(request.getEndDate())) {
                    query.setParameter("endDate", request.getEndDate());
                } else {
                    if (CommonUtils.isNotEmpty(request.getStartDate())) {
                        query.setParameter("endDate", request.getStartDate());
                    }
                }
                if (CommonUtils.isNotEmpty(request.getDepartmentId())) {
                    query.setParameter("departmentId", request.getDepartmentId());
                }
                if (CommonUtils.isNotEmpty(request.getResultCode())) {
                    query.setParameter("resultCode", request.getResultCode());
                }
                if (request.getFollowUpStatus() != null) {
                    query.setParameter("followUpStatus", request.getFollowUpStatus());
                }
                if (StringUtils.isNotEmpty(request.getAnswerAbnormal())) {
                    query.setParameter("answerAbnormal", request.getAnswerAbnormal());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

                List<Map> dbList = query.list();

                if (CommonUtils.isNotEmpty(dbList)) {
                    setResult(Long.valueOf(String.valueOf(dbList.get(0).get("countValue"))));
                } else {
                    setResult(0L);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * @param
     * @return java.lang.Long
     * @description: 获取随访计划记录总记录数且专科随访的
     * @author zangsf
     * @date 2021-12-16 14:33
     */
    public Long queryExecutionListByTeamIdCount(final QueryExecutionListRequest request) {
        HibernateStatelessResultAction<Long> action = new AbstractHibernateStatelessResultAction<Long>() {
            @Override
            public void execute(StatelessSession ss) {
                StringBuilder sqlBuilder = new StringBuilder();
                sqlBuilder.append(" select count(1) countValue from follow_up_records where 1=1 ");
                if (CommonUtils.isNotEmpty(request.getOrganizationId())) {
                    sqlBuilder.append(" and organization_id = :organizationId ");
                }
                if (CommonUtils.isNotEmpty(request.getDiseaseTypeId())) {
                    sqlBuilder.append(" and disease_type_id = :diseaseTypeId ");
                }
                if (CommonUtils.isNotEmpty(request.getRouteDetailId())) {
                    sqlBuilder.append(" and route_detail_id = :routeDetailId ");
                }
                if (CommonUtils.isNotEmpty(request.getRouteId())) {
                    sqlBuilder.append(" and route_id = :routeId ");
                }
                if (CommonUtils.isNotEmpty(request.getDoctorId())) {
                    sqlBuilder.append(" and doctor_id = :doctorId ");
                }
                if (CommonUtils.isNotEmpty(request.getDocId())) {
                    sqlBuilder.append(" and doc_id != :docId ");
                }
                if (CommonUtils.isNotEmpty(request.getTeamChronicDiseaseIdList())){
                    sqlBuilder.append(" and disease_type_id in:teamChronicDiseaseIdList ");
                }
                if (CommonUtils.isNotEmpty(request.getRouteDetailType())) {
                    sqlBuilder.append(" and route_detail_type = :routeDetailType ");
                }
                if (CommonUtils.isNotEmpty(request.getPatientName())) {
                    sqlBuilder.append(" and (patient_name like :patientName or patient_medical_card_number like" +
                            " " +
                            ":patientName) ");
                }
                if (CommonUtils.isNotEmpty(request.getStartDate())) {
                    sqlBuilder.append(" and plan_time >= :startDate ");
                }
                if (CommonUtils.isNotEmpty(request.getEndDate())) {
                    sqlBuilder.append(" and plan_time <= :endDate ");
                } else {
                    if (CommonUtils.isNotEmpty(request.getStartDate())) {
                        sqlBuilder.append(" and plan_time <= :endDate ");
                    }
                }
                if (CommonUtils.isNotEmpty(request.getDepartmentId())) {
                    sqlBuilder.append(" and department_id = :departmentId ");
                }
                if (CommonUtils.isNotEmpty(request.getResultCode())) {
                    sqlBuilder.append(" and result_code = :resultCode ");
                }
                if (request.getFollowUpStatus() != null) {
                    sqlBuilder.append(" and follow_up_status = :followUpStatus ");
                }
                if (StringUtils.isNotEmpty(request.getAnswerAbnormal())) {
                    sqlBuilder.append(" and answer_abnormal = :answerAbnormal and abnormal_read_flag='0' ");
                }

                Query query = ss.createSQLQuery(sqlBuilder.toString());
                if (CommonUtils.isNotEmpty(request.getOrganizationId())) {
                    query.setParameter("organizationId", request.getOrganizationId());
                }
                if (CommonUtils.isNotEmpty(request.getDiseaseTypeId())) {
                    query.setParameter("diseaseTypeId", request.getDiseaseTypeId());
                }
                if (CommonUtils.isNotEmpty(request.getRouteDetailId())) {
                    query.setParameter("routeDetailId", request.getRouteDetailId());
                }
                if (CommonUtils.isNotEmpty(request.getRouteId())) {
                    query.setParameter("routeId", request.getRouteId());
                }
                if (CommonUtils.isNotEmpty(request.getDoctorId())) {
                    query.setParameter("doctorId", request.getDoctorId());
                }
                if (CommonUtils.isNotEmpty(request.getDocId())) {
                    query.setParameter("docId", request.getDocId());
                }
                if (CommonUtils.isNotEmpty(request.getTeamChronicDiseaseIdList())){
                    query.setParameterList("teamChronicDiseaseIdList", request.getTeamChronicDiseaseIdList());
                }
                if (CommonUtils.isNotEmpty(request.getRouteDetailType())) {
                    query.setParameter("routeDetailType", request.getRouteDetailType());
                }
                if (CommonUtils.isNotEmpty(request.getPatientName())) {
                    query.setParameter("patientName", request.getPatientName() + "%");
                }
                if (CommonUtils.isNotEmpty(request.getStartDate())) {
                    query.setParameter("startDate", request.getStartDate());
                }
                if (CommonUtils.isNotEmpty(request.getEndDate())) {
                    query.setParameter("endDate", request.getEndDate());
                } else {
                    if (CommonUtils.isNotEmpty(request.getStartDate())) {
                        query.setParameter("endDate", request.getStartDate());
                    }
                }
                if (CommonUtils.isNotEmpty(request.getDepartmentId())) {
                    query.setParameter("departmentId", request.getDepartmentId());
                }
                if (CommonUtils.isNotEmpty(request.getResultCode())) {
                    query.setParameter("resultCode", request.getResultCode());
                }
                if (request.getFollowUpStatus() != null) {
                    query.setParameter("followUpStatus", request.getFollowUpStatus());
                }
                if (StringUtils.isNotEmpty(request.getAnswerAbnormal())) {
                    query.setParameter("answerAbnormal", request.getAnswerAbnormal());
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);

                List<Map> dbList = query.list();

                if (CommonUtils.isNotEmpty(dbList)) {
                    setResult(Long.valueOf(String.valueOf(dbList.get(0).get("countValue"))));
                } else {
                    setResult(0L);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * @param userInfoId
     * @throws
     * @description: 根据问卷答案Id查询随访计划记录
     * @return: specialist.entity.FollowUpRecords
     * @author: tongtp
     * @date: 2020/8/4 17:05
     */
    @DAOMethod(sql = " from FollowUpRecords where userInfoId = :userInfoId")
    public abstract FollowUpRecords getByUserInfoId(@DAOParam("userInfoId") String userInfoId);

    @DAOMethod(sql = " from  FollowUpRecords where cardType =:cardType and cardNumber=:cardNumber")
    public abstract List<FollowUpRecords> queryFollowUpRecordsByPerson(@DAOParam("cardType") String cardType,
                                                                       @DAOParam("cardNumber") String cardNumber,
                                                                       @DAOParam(pageStart = true) int start,
                                                                       @DAOParam(pageLimit = true) int limit);

    @DAOMethod(sql = "update FollowUpRecords set medicalTeamId = :medicalTeamId,medicalTeamName = :medicalTeamName," +
            "diseaseTypeId = :diseaseTypeId,diseaseTypeName = :diseaseTypeName " +
            ",docId = :docId,doctorId = :doctorId,doctorName = :doctorName " +
            " where teamChronicGroupId = :teamChronicGroupId")
    public abstract void updateDocInfoByGroupId(@DAOParam("medicalTeamId") Long medicalTeamId,
                                                @DAOParam("medicalTeamName") String medicalTeamName,
                                                @DAOParam("diseaseTypeId") Long diseaseTypeId,
                                                @DAOParam("diseaseTypeName") String diseaseTypeName,
                                                @DAOParam("docId") String docId,
                                                @DAOParam("doctorId") String doctorId,
                                                @DAOParam("doctorName") String doctorName,
                                                @DAOParam("teamChronicGroupId") Long teamChronicGroupId);


    @DAOMethod(sql = " from  FollowUpRecords where cardType =:cardType and cardNumber=:cardNumber")
    public abstract List<FollowUpRecords> queryFollowUpRecordsByCard(@DAOParam("cardType") String cardType,
                                                                     @DAOParam("cardNumber") String cardNumber);

    @DAOMethod(sql = "select COUNT(1) from FollowUpRecords where planTime > :planTime and followUpStatus = " +
            ":followUpStatus and teamChronicGroupId = :teamChronicGroupId and routeDetailId = :routeDetailId")
    public abstract Long getCountByPlanTimeAndStatus(@DAOParam("planTime") Date planTime,
                                                     @DAOParam("followUpStatus") Byte followUpStatus,
                                                     @DAOParam("teamChronicGroupId") Long teamChronicGroupId,
                                                     @DAOParam("routeDetailId") Long routeDetailId);


    @DAOMethod(sql = "select COUNT(1) from FollowUpRecords where  diseaseTypeId = :diseaseTypeId")
    public abstract Long getCountByDiseaseTypeId(@DAOParam("diseaseTypeId") Long diseaseTypeId);

    @DAOMethod(sql = "update FollowUpRecords set followUpStatus = :newFollowUpStatus,stopTime = :now,stopId = " +
            ":stopId where planTime > :now and followUpStatus = :followUpStatus and teamChronicGroupId" +
            " = :teamChronicGroupId and routeDetailId = :routeDetailId")
    public abstract void updateByPlanTimeAndStatus(@DAOParam("now") Date now,
                                                   @DAOParam("followUpStatus") Byte followUpStatus,
                                                   @DAOParam("teamChronicGroupId") Long teamChronicGroupId,
                                                   @DAOParam("routeDetailId") Long routeDetailId,
                                                   @DAOParam("newFollowUpStatus") Byte newFollowUpStatus,
                                                   @DAOParam("stopId") String stopId);

    /**
     * @param now
     * @param followUpStatus
     * @param newFollowUpStatus
     * @param stopId
     * @param applyStatus
     * @throws
     * @description: 更新已经出组的随访执行计划
     * @return: java.lang.Integer
     * @author: tongtp
     * @date: 2020/8/27 9:22
     */
    public Integer updateByOutGroup(final Date now, final Byte followUpStatus,
                                    final Byte newFollowUpStatus, final String stopId,
                                    final String applyStatus) {
        HibernateStatelessResultAction<Integer> action =
                new AbstractHibernateStatelessResultAction<Integer>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append("update follow_up_records AS a  INNER JOIN fus_team_chronic_group_record " +
                                "AS" +
                                " b on a.team_chronic_group_id = b.team_chronic_group_id AND b.apply_status = " +
                                ":applyStatus set a.follow_up_status = " +
                                ":newFollowUpStatus," +
                                "a.stop_time = :now,a.stop_id = :stopId" +
                                " where a.follow_up_status = :followUpStatus");
                        Query query = ss.createSQLQuery(sqlBuilder.toString());
                        query.setParameter("now", now);
                        query.setParameter("stopId", stopId);
                        query.setParameter("newFollowUpStatus", newFollowUpStatus);
                        query.setParameter("followUpStatus", followUpStatus);
                        query.setParameter("applyStatus", applyStatus);
                        int executeUpdate = query.executeUpdate();
                        setResult(executeUpdate);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<FollowUpRecords> queryDateGroupList(final String cardType, final String cardNumber, final String today) {
        HibernateStatelessResultAction<List<FollowUpRecords>> action =
                new AbstractHibernateStatelessResultAction<List<FollowUpRecords>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuffer sql = new StringBuffer();
                        sql.append("select plan_time as planTime from follow_up_records where card_number=:cardNumber and card_type=:cardType and plan_time <=:today " +
                                " and follow_up_status in(0,1,2) and route_detail_type in (1,3,4,6,8) and auto_create_flag='0' group by plan_time order by plan_time desc ");
                        Query query = ss.createSQLQuery(sql.toString());
                        query.setParameter("cardType", cardType);
                        query.setParameter("cardNumber", cardNumber);
                        query.setParameter("today", today);
                        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        List<FollowUpRecords> followUpPlanList = new ArrayList<FollowUpRecords>();
                        List<Map> list = query.list();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                followUpPlanList.add(ConversionUtils.convert(map, FollowUpRecords.class));
                            }
                            setResult(followUpPlanList);
                        } else {
                            setResult(null);
                        }
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<QueryPlanListResponse.FollowUpDatePlan> queryPlanList(final String cardType, final String cardNumber, final Date planTime, final String today) {
        HibernateStatelessResultAction<List<QueryPlanListResponse.FollowUpDatePlan>> action =
                new AbstractHibernateStatelessResultAction<List<QueryPlanListResponse.FollowUpDatePlan>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuffer sql = new StringBuffer();
                        sql.append("select a.record_id as recordId, a.organization_id as organizationId, a.plan_time as planTime,a.route_detail_id as routeDetailId" +
                                ",a.route_detail_type as routeDetailType,a.user_info_id as userInfoId,a.route_id as routeId,a.department_id as departmentId");
                        sql.append(",a.department_name as departmentName,a.follow_up_status as followUpStatus,a.medical_record_id as medicalRecordId,a.medical_record_name as medicalRecordName ");
                        sql.append(" from follow_up_records a where a.card_number=:cardNumber and a.card_type =:cardType and a.plan_time=:planTime and a.follow_up_status in (0,1,2) ");
                        sql.append(" and a.route_detail_type in (1,3,4,6,8) and auto_create_flag='0' order by a.plan_time desc");
                        Query query = ss.createSQLQuery(sql.toString());
                        query.setParameter("cardType", cardType);
                        query.setParameter("cardNumber", cardNumber);
                        query.setParameter("planTime", planTime);
//                        query.setParameter("today", today);
                        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        List<QueryPlanListResponse.FollowUpDatePlan> followUpPlanList = Lists.newArrayList();
                        List<Map> list = query.list();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                followUpPlanList.add(ConversionUtils.convert(map, QueryPlanListResponse.FollowUpDatePlan.class));
                            }
                            setResult(followUpPlanList);
                        } else {
                            setResult(null);
                        }
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<QueryDoctorListResponse> queryDoctorList(final String cardType, final String cardNumber) {
        HibernateStatelessResultAction<List<QueryDoctorListResponse>> action =
                new AbstractHibernateStatelessResultAction<List<QueryDoctorListResponse>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuffer sql = new StringBuffer();
                        sql.append("select DISTINCT(f.medical_team_id) as medicalTeamId ,f.doctor_id as doctorManagerId,f.organization_id as  organizationId, f.organization_name as organizationName, ");
                        sql.append(" f.org_id as orgId, f.dept_id as deptId ");

                        sql.append("from fus_team_chronic_group_record f ");
                        sql.append("where f.apply_status='1' and   f.card_number=:cardNumber and f.card_type=:cardType");
                        Query query = ss.createSQLQuery(sql.toString());
                        query.setParameter("cardType", cardType);
                        query.setParameter("cardNumber", cardNumber);
                        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        List<QueryDoctorListResponse> followUpPlanList = new ArrayList<QueryDoctorListResponse>();
                        List<Map> list = query.list();
                        if (list.size() > 0) {
                            for (Map map : list) {

                                followUpPlanList.add(ConversionUtils.convert(map, QueryDoctorListResponse.class));
                            }
                            setResult(followUpPlanList);
                        } else {
                            setResult(null);
                        }
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * 获取我的待随访任务总数
     *
     * @param patientId
     * @return
     */
    public QueryPatientFollowupTotalResp queryMyFollowUpTaskTotal(final String patientId, final Date today) {
        HibernateStatelessResultAction<QueryPatientFollowupTotalResp> action =
                new AbstractHibernateStatelessResultAction<QueryPatientFollowupTotalResp>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuffer sql = new StringBuffer();
                        sql.append("SELECT count(1) followUpTaskTotal FROM follow_up_records t where t" +
                                ".follow_up_status in (0,2) ");
                        sql.append(" and is_notify='1' and is_read=0  and route_detail_type not in (2,5) and plan_notify_date<=:today  ");
                        sql.append(" and t.patient_id=:patientId ");
                        Query query = ss.createSQLQuery(sql.toString());
                        query.setParameter("patientId", patientId);
                        query.setParameter("today", today);
                        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        List<Map> list = query.list();
                        if (list.size() == 1) {
                            Object o = list.get(0);
                            QueryPatientFollowupTotalResp queryPatientFollowupTotalResp = ConversionUtils.convert(o,
                                    QueryPatientFollowupTotalResp.class);
                            setResult(queryPatientFollowupTotalResp);
                        }
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    @DAOMethod(sql = "select count(1) from  FollowUpRecords where route_id = :routeId and follow_up_status = 0")
    public abstract Long getRecordsCount(@DAOParam("routeId") Long routeId);

    public Long countExecutionListByPatient(final QueryExecutionListByPatientReq byPatientReq) {
        HibernateStatelessResultAction<Long> action = new AbstractHibernateStatelessResultAction<Long>() {
            @Override
            public void execute(StatelessSession ss) {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.append("SELECT count(*) countValue ");
                getExecutionListByPatientStringBuilder(stringBuilder, byPatientReq);
                Query sqlQuery = getQuery(ss, byPatientReq, stringBuilder);
                sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> dbList = sqlQuery.list();
                if (CommonUtils.isNotEmpty(dbList)) {
                    setResult(Long.valueOf(String.valueOf(dbList.get(0).get("countValue"))));
                } else {
                    setResult(0L);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    private StringBuilder getExecutionListByPatientStringBuilder(StringBuilder stringBuilder,
                                                                 final QueryExecutionListByPatientReq request) {
        stringBuilder.append(" from ( ");
        if (request.getFollowupType().contains(FollowTypeEnum.FUS_SPECIALIST.getType())) {
            //包含专病随访
            stringBuilder.append("SELECT a.record_id recordId, a.route_detail_type routeDetailType, ");
            stringBuilder.append("'1' followType,a.execute_time executeTime,a.follow_up_status + '' as followUpStatus,a.plan_time planTime ,a.disease_type_name diseaseTypeName, " +
                    "a.doctor_id doctorId,a.doctor_name doctorName ,route_name routeName,' ' followUpDetailType ,a.medical_team_name as medicalTeamName");
            stringBuilder.append(" FROM follow_up_records a where 1=1  ");
            if (request.getFollowUpStatus() != null) {
                if (request.getFollowUpStatus() == 0) {
                    stringBuilder.append("  and a.execute_time is null ");
                } else if (request.getFollowUpStatus() == 1) {
                    stringBuilder.append(" and a.execute_time is not null ");
                }
            }
            if (StringUtils.isNotEmpty(request.getOrganizationId())) {
                stringBuilder.append(" and a.organization_id=:orgId");
            }
            if (StringUtils.isNotEmpty(request.getDoctorId())) {
                stringBuilder.append(" and a.doctor_id=:doctorId");
            }
            if (StringUtils.isNotEmpty(request.getDocId())) {
                stringBuilder.append(" and a.doc_id=:docId");
            }
            if (StringUtils.isNotEmpty(request.getStartTime())) {
                stringBuilder.append(" and a.plan_time>= :startTime ");
            }
            if (StringUtils.isNotEmpty(request.getEndTime())) {
                stringBuilder.append(" and a.plan_time<= :endTime  ");
            }
            if (StringUtils.isNotEmpty(request.getPatientId())) {
                if (StringUtils.isNotEmpty(request.getCardType()) && StringUtils.isNotEmpty(request.getCardNumber())) {
                    stringBuilder.append(" and ( a.patient_id=:patientId ");
                    stringBuilder.append(" or  (a.card_type = :cardType and a.card_number =:cardNumber )) ");
                } else {
                    stringBuilder.append(" and  a.patient_id=:patientId ");
                }
            } else {
                stringBuilder.append(" and (a.card_type = :cardType and a.card_number =:cardNumber )");
            }
            if (CommonUtils.isNotEmpty(request.getDiseaseTypeId())) {
                stringBuilder.append(" and a.disease_type_id = :diseaseTypeId ");
            }
            if (request.getFollowupType().contains(FollowTypeEnum.FUS_AFTER.getType()) || request.getFollowupType().contains(FollowTypeEnum.FUS_SATISFACTION.getType())) {
                stringBuilder.append(" UNION ALL  ");
            }
        }
        if (request.getFollowupType().contains(FollowTypeEnum.FUS_AFTER.getType())) {
            //院后随访
            stringBuilder.append(" SELECT b.record_id recordId, b.route_detail_type routeDetailType ,  ");
            stringBuilder.append("'2' followType,b.execute_time executeTime ,b.follow_up_status + '' as followUpStatus,b.plan_time planTime, ' ' diseaseTypeName, ");
            stringBuilder.append(" doctor_id doctorId,doctor_name doctorName,route_name routeName ,patient_type followUpDetailType ,' ' medicalTeamName ");
            stringBuilder.append(" FROM fus_after_hos_record b where b.execute_time is not null ");
            if (StringUtils.isNotEmpty(request.getOrganizationId())) {
                stringBuilder.append(" and b.organization_id=:orgId");
            }
            if (StringUtils.isNotEmpty(request.getDoctorId())) {
                stringBuilder.append(" and b.doctor_id=:doctorId");
            }
            if (StringUtils.isNotEmpty(request.getStartTime())) {
                stringBuilder.append(" and b.plan_time>= :startTime");
            }
            if (StringUtils.isNotEmpty(request.getEndTime())) {
                stringBuilder.append(" and b.plan_time<= :endTime  ");
            }
            if (StringUtils.isNotEmpty(request.getPatientId())) {
                if (StringUtils.isNotEmpty(request.getCardType()) && StringUtils.isNotEmpty(request.getCardNumber())) {
                    stringBuilder.append(" and ( b.patient_id=:patientId ");
                    stringBuilder.append(" or  (b.card_type = :cardType and b.card_number =:cardNumber )) ");
                } else {
                    stringBuilder.append(" and  b.patient_id=:patientId ");
                }
            } else {
                stringBuilder.append(" and (b.card_type = :cardType and b.card_number =:cardNumber ) ");
            }
            if (request.getFollowupType().contains(FollowTypeEnum.FUS_SATISFACTION.getType())) {
                stringBuilder.append(" UNION ALL ");
            }
        }
        if (request.getFollowupType().contains(FollowTypeEnum.FUS_SATISFACTION.getType())) {
            stringBuilder.append("SELECT c.patient_survey_id recordId, e.route_detail_type routeDetailType, ");
            stringBuilder.append("'3' followType,c.survey_time executeTime, c.survey_status followUpStatus,c.plan_survey_time planTime, ' ' diseaseTypeName, ");
            stringBuilder.append(" doctor_id doctorId,doctor_name doctorName,survey_rule_name routeName ,c.business_type followUpDetailType,' ' medicalTeamName ");
            stringBuilder.append(" FROM patient_satisfaction_survey c LEFT JOIN satisfaction_produce_record d ");
            stringBuilder.append("on c.patient_survey_id = d.patient_survey_id LEFT JOIN " +
                    "follow_up_route_detail e ");

            stringBuilder.append(" on d.route_detail_id = e.route_detail_id where c.survey_time is not null");

            if (StringUtils.isNotEmpty(request.getOrganizationId())) {
                stringBuilder.append(" and c.organization_id=:orgId");
            }
            if (StringUtils.isNotEmpty(request.getDoctorId())) {
                stringBuilder.append(" and c.doctor_id=:doctorId");
            }
            if (StringUtils.isNotEmpty(request.getStartTime())) {
                stringBuilder.append(" and c.plan_survey_time>= :startTime");
            }
            if (StringUtils.isNotEmpty(request.getEndTime())) {
                stringBuilder.append(" and c.plan_survey_time<= :endTime  ");
            }
            if (StringUtils.isNotEmpty(request.getPatientId())) {
                if (StringUtils.isNotEmpty(request.getCardType()) && StringUtils.isNotEmpty(request.getCardNumber())) {
                    stringBuilder.append(" and ( c.patient_id=:patientId ");
                    stringBuilder.append(" or  (c.card_type = :cardType and c.card_number =:cardNumber )) ");
                } else {
                    stringBuilder.append(" and  c.patient_id=:patientId ");
                }
            } else {
                stringBuilder.append(" and (c.card_type = :cardType and c.card_number =:cardNumber )");
            }
        }
        stringBuilder.append(" ) m order by executeTime desc");
        return stringBuilder;
    }

    private Query getQueryByContentList(StatelessSession ss, final QueryExecutionListByPatientReq byPatientReq,
                                        StringBuffer stringBuffer) {
        Query sqlQuery = ss.createSQLQuery(stringBuffer.toString());
        if (StringUtils.isNotEmpty(byPatientReq.getOrganizationId())) {
            sqlQuery.setParameter("orgId", byPatientReq.getOrganizationId());
        }
        if (byPatientReq.getFollowUpStatus() != null && CommonUtils.isEmpty(byPatientReq.getFollowUpStatusList())) {//状态
            sqlQuery.setParameter("followUpStatus", byPatientReq.getFollowUpStatus());
        }
        if (CommonUtils.isNotEmpty(byPatientReq.getFollowUpStatusList())) {//状态
            sqlQuery.setParameterList("followUpStatusList", byPatientReq.getFollowUpStatusList());
        }
        if (StringUtils.isNotEmpty(byPatientReq.getPatientId())) {//患者唯一编码
            sqlQuery.setParameter("patientId", byPatientReq.getPatientId());
        }
		/*if (StringUtils.isNotEmpty(byPatientReq.getCardType())) {//证件类型
			sqlQuery.setParameter("cardType", byPatientReq.getCardType());
		}*/
        if (StringUtils.isNotEmpty(byPatientReq.getCardNumber())) {//证件号码
            sqlQuery.setParameter("cardNumber", byPatientReq.getCardNumber());
        }
        if (CommonUtils.isNotEmpty(byPatientReq.getDiseaseTypeId())) {
            sqlQuery.setParameter("diseaseTypeId", byPatientReq.getDiseaseTypeId());
        }
        return sqlQuery;
    }

    private Query getQuery(StatelessSession ss, final QueryExecutionListByPatientReq byPatientReq,
                           StringBuilder stringBuilder) {
        Query sqlQuery = ss.createSQLQuery(stringBuilder.toString());
        if (StringUtils.isNotEmpty(byPatientReq.getOrganizationId())) {
            sqlQuery.setParameter("orgId", byPatientReq.getOrganizationId());
        }
        if (StringUtils.isNotEmpty(byPatientReq.getPatientId())) {
            sqlQuery.setParameter("patientId", byPatientReq.getPatientId());
        }
        if (StringUtils.isNotEmpty(byPatientReq.getDoctorId())) {
            sqlQuery.setParameter("doctorId", byPatientReq.getDoctorId());
        }
        if (StringUtils.isNotEmpty(byPatientReq.getDocId())) {
            sqlQuery.setParameter("docId", byPatientReq.getDocId());
        }
        if (StringUtils.isNotEmpty(byPatientReq.getStartTime())){
            sqlQuery.setParameter("startTime",byPatientReq.getStartTime());
        }
        if (StringUtils.isNotEmpty(byPatientReq.getEndTime())){
            sqlQuery.setParameter("endTime",byPatientReq.getEndTime());
        }
        if (StringUtils.isNotEmpty(byPatientReq.getPatientId())) {
            if (StringUtils.isNotEmpty(byPatientReq.getCardType()) && StringUtils.isNotEmpty(byPatientReq.getCardNumber())) {
                sqlQuery.setParameter("cardType", byPatientReq.getCardType());
                sqlQuery.setParameter("cardNumber", byPatientReq.getCardNumber());
            }
        } else {
            sqlQuery.setParameter("cardType", byPatientReq.getCardType());
            sqlQuery.setParameter("cardNumber", byPatientReq.getCardNumber());
        }
        if (CommonUtils.isNotEmpty(byPatientReq.getDiseaseTypeId())) {
            sqlQuery.setParameter("diseaseTypeId", byPatientReq.getDiseaseTypeId());
        }
        return sqlQuery;
    }

    public List<ExecutionListByPatientResp> queryExecutionListByPatient(final QueryExecutionListByPatientReq byPatientReq) {
        HibernateStatelessResultAction<List<ExecutionListByPatientResp>> action =
                new AbstractHibernateStatelessResultAction<List<ExecutionListByPatientResp>>() {
                    @Override
                    public void execute(StatelessSession ss) {
                        StringBuilder stringBuilder = new StringBuilder();
                        stringBuilder.append(" select m.recordId,m.routeDetailType,m.followType,m.executeTime,m.followUpStatus,m.planTime,m.diseaseTypeName," +
                                " m.doctorId,m.doctorName,m.routeName,m.followUpDetailType,m.medicalTeamName");
                        getExecutionListByPatientStringBuilder(stringBuilder, byPatientReq);
                        Query sqlQuery = getQuery(ss, byPatientReq, stringBuilder);
                        int first = byPatientReq.getPageNo() > 0 ? (byPatientReq.getPageNo() - 1) * byPatientReq.getPageSize() : 0;
                        sqlQuery.setFirstResult(first);
                        sqlQuery.setMaxResults(byPatientReq.getPageSize());
                        sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        List<Map<String,Object>> dbList = sqlQuery.list();
                        setResult(mapToList(dbList));
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    private List<ExecutionListByPatientResp> mapToList(List<Map<String,Object>> maps) {
        List<ExecutionListByPatientResp> executionList = new ArrayList<>();
        for (Map map : maps) {
            ExecutionListByPatientResp convert = ConversionUtils.convert(map, ExecutionListByPatientResp.class);
            convert.setFollowUpStatus(Double.valueOf(convert.getFollowUpStatus()).intValue());
            executionList.add(convert);
        }
        return executionList;
    }

    public List<FollowUpRecordsContent> queryContentListByPatient(final QueryExecutionListByPatientReq byPatientReq) {
        HibernateStatelessResultAction<List<FollowUpRecordsContent>> action =
                new AbstractHibernateStatelessResultAction<List<FollowUpRecordsContent>>() {
                    @Override
                    public void execute(StatelessSession ss) {
                        StringBuffer stringBuffer = getContentListByPatient(byPatientReq);
                        Query sqlQuery = getQueryByContentList(ss, byPatientReq, stringBuffer);
                        int first = byPatientReq.getPageNo() > 0 ?
                                (byPatientReq.getPageNo() - 1) * byPatientReq.getPageSize() : 0;
                        sqlQuery.setFirstResult(first);
                        sqlQuery.setMaxResults(byPatientReq.getPageSize());
                        sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        List<Map> dbList = sqlQuery.list();
                        setResult(mapToContentList(dbList));
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    private StringBuffer getContentListByPatient(final QueryExecutionListByPatientReq byPatientReq) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT c.follow_up_records_content_id followUpRecordsContentId,");
        stringBuffer.append("c.record_id recordId,");
        stringBuffer.append("c.index_type indexType,");
        stringBuffer.append("c.index_classify_id indexClassifyId,");
        stringBuffer.append("c.index_classify_name indexClassifyName,");
        stringBuffer.append("c.business_id businessId,");
        stringBuffer.append("c.business_name businessName,");
        stringBuffer.append("c.follow_up_detail followUpDetail,");
        stringBuffer.append("c.education_id educationId,");
        stringBuffer.append("c.education_name educationName ");
        stringBuffer.append("FROM follow_up_records_content c  LEFT JOIN follow_up_records r ON c.record_id=r.record_id ");
        stringBuffer.append("WHERE 1 = 1");
        if (StringUtils.isNotEmpty(byPatientReq.getOrganizationId())) {
            stringBuffer.append(" and r.organization_id=:orgId");
        }
        if (byPatientReq.getFollowUpStatus() != null && CommonUtils.isEmpty(byPatientReq.getFollowUpStatusList())) {
            stringBuffer.append(" and follow_up_status=:followUpStatus");
        }
        if (CommonUtils.isNotEmpty(byPatientReq.getFollowUpStatusList())) {
            stringBuffer.append(" and follow_up_status in(:followUpStatusList)");
        }
        if (StringUtils.isNotEmpty(byPatientReq.getPatientId())) {
            stringBuffer.append(" and r.patient_id=:patientId");
        }
        if (StringUtils.isNotEmpty(byPatientReq.getCardNumber())) {
            stringBuffer.append(" and r.card_number =:cardNumber");
        }
        if (CommonUtils.isNotEmpty(byPatientReq.getDiseaseTypeId())) {
            stringBuffer.append(" and r.disease_type_id =:diseaseTypeId");
        }
        return stringBuffer;
    }

    private List<FollowUpRecordsContent> mapToContentList(List<Map> maps) {
        List<FollowUpRecordsContent> executionList = new ArrayList<>();
        for (Map map : maps) {
            executionList.add(ConversionUtils.convert(map, FollowUpRecordsContent.class));
        }
        return executionList;
    }

    /**
     * 获取异常随访计划
     *
     * @param localOrgId 机构编码
     * @param docId      医生id
     * @param startDate  时间
     * @param endDate    时间
     * @return 计划集合
     */
    public List<FollowUpRecordsVO> queryAbnormalFollowupList(final String localOrgId, final String docId, final String startDate, final String endDate) {
        HibernateStatelessResultAction<List<FollowUpRecordsVO>> action =
                new AbstractHibernateStatelessResultAction<List<FollowUpRecordsVO>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuffer sql = new StringBuffer();
                        sql.append(" select t.record_id as recordId,t.patient_id as patientId,t.patient_name as patientName,t.medical_record_id as medicalRecordId,t.medical_record_name as medicalRecordName ");
                        sql.append(" ,t.plan_time as planTime,t.result_code as resultCode,t.follow_up_status as followUpStatus,t.abnormal_read_flag as abnormalReadFlag,t.user_info_id as userInfoId ");
                        sql.append(" ,disease_type_name as diseaseTypeName,disease_type_id as diseaseTypeId,route_detail_type as routeDetailType,t.medical_record_upload_flag as medicalRecordUploadFlag ");
                        sql.append(" ,t.execute_id as executeId,t.execute_time as executeTime,t.execute_name as executeName,t.team_chronic_group_id as teamChronicGroupId ");
                        sql.append(" from follow_up_records t where t.organization_id=:localOrgId and t.doc_id=:docId and t.follow_up_status in (1,2) ");
                        if (CommonUtils.isNotEmpty(startDate)) {
                            sql.append(" and t.execute_time >=:startDate ");
                        }
                        if (CommonUtils.isNotEmpty(endDate)) {
                            sql.append(" and t.execute_time <=:endDate ");
                        }
                        sql.append(" and t.abnormal_read_flag ='0' and t.answer_abnormal ='1' order by t.execute_time desc ");
                        Query query = ss.createSQLQuery(sql.toString());
                        query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                        query.setParameter("localOrgId", localOrgId);
                        query.setParameter("docId", docId);
                        if (CommonUtils.isNotEmpty(startDate)) {
                            query.setParameter("startDate", startDate);
                        }
                        if (CommonUtils.isNotEmpty(endDate)) {
                            query.setParameter("endDate", endDate);
                        }
                        List<Map> list = query.list();
                        List<FollowUpRecordsVO> queryDoctorFollowupInfoList = Lists.newArrayList();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                queryDoctorFollowupInfoList.add(ConversionUtils.convert(map, FollowUpRecordsVO.class));
                            }
                        }
                        setResult(queryDoctorFollowupInfoList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * 获取待执行和待确认的随访计划
     *
     * @param localOrgId 机构编码
     * @param docId      医生id
     * @param startDate  时间
     * @return 计划集合
     */
    public List<FollowUpRecordsVO> queryToPerformFollowupList(final String localOrgId, final String docId, final String startDate, final List<Long> medicalTeamId,final List<String> teamChronicDiseaseIdList) {
        HibernateStatelessResultAction<List<FollowUpRecordsVO>> action = new AbstractHibernateStatelessResultAction<List<FollowUpRecordsVO>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();

                sql.append(" select t.record_id as recordId,t.patient_id as patientId,t.patient_name as patientName,t.medical_record_id as medicalRecordId,t.medical_record_name as medicalRecordName ");
                sql.append(" ,t.plan_time as planTime,t.result_code as resultCode,t.follow_up_status as followUpStatus,t.medical_record_upload_flag as medicalRecordUploadFlag");
                sql.append(" ,disease_type_name as diseaseTypeName,disease_type_id as diseaseTypeId,route_detail_type as routeDetailType,t.user_info_id as userInfoId ");
                sql.append(" ,t.execute_id as executeId,t.execute_time as executeTime,t.execute_name as executeName,t.team_chronic_group_id as teamChronicGroupId ");
                sql.append(" from follow_up_records t where t.follow_up_status in(0,2) ");
//                if (CommonUtils.isNotEmpty(startDate)) {
//                    sql.append(" and t.plan_time <=:startDate ");
//                }
                if (CommonUtils.isNotEmpty(medicalTeamId)) {
                    sql.append(" and t.medical_team_id in (:medicalTeamId) ");
                }
                if (CommonUtils.isNotEmpty(docId)) {
                    sql.append(" and t.doc_id=:docId ");
                }
                if (CommonUtils.isNotEmpty(teamChronicDiseaseIdList)){
                    sql.append(" and disease_type_id in:teamChronicDiseaseIdList ");
                }

                Query query = ss.createSQLQuery(sql.toString());
//                query.setParameter("localOrgId", localOrgId);
//                if (CommonUtils.isNotEmpty(startDate)) {
//                    query.setParameter("startDate", startDate);
//                }
                if (CommonUtils.isNotEmpty(docId)) {
                    query.setParameter("docId", docId);
                }
                if (CommonUtils.isNotEmpty(medicalTeamId)) {
                    query.setParameterList("medicalTeamId", medicalTeamId);
                }
                if (CommonUtils.isNotEmpty(teamChronicDiseaseIdList)) {
                    query.setParameterList("teamChronicDiseaseIdList", teamChronicDiseaseIdList);
                }

                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<FollowUpRecordsVO> followupInfoList = Lists.newArrayList();
                List<Map> list = query.list();
                if (CommonUtils.isNotEmpty(list)) {
                    for (Map map : list) {
                        followupInfoList.add(ConversionUtils.convert(map, FollowUpRecordsVO.class));
                    }
                }
                setResult(followupInfoList);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * 获取我的入组申请集合(区分入组方式，保持与前端web逻辑一致，查询扫描入组)
     *
     * @param docId
     * @return
     */
    public List<QueryDoctorFollowupResp.patientInfo> queryGroupAuditList(final String docId) {
        HibernateStatelessResultAction<List<QueryDoctorFollowupResp.patientInfo>> action = new AbstractHibernateStatelessResultAction<List<QueryDoctorFollowupResp.patientInfo>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("  SELECT person_id as patientId, person_name as patientName, chronic_disease_name as diseaseName, " +
                        "apply_time as time from fus_team_chronic_group_record t WHERE t.doc_id=:docId ");
                sql.append("  and t.apply_type='1' and apply_status='0' order by apply_time asc ");
                Query query = ss.createSQLQuery(sql.toString());
                query.setParameter("docId", docId);

                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                List<QueryDoctorFollowupResp.patientInfo> patientInfoList = Lists.newArrayList();
                if (CommonUtils.isNotEmpty(list)) {
                    for (Map map : list) {
                        QueryDoctorFollowupResp.patientInfo patientInfo = ConversionUtils.convert(map, QueryDoctorFollowupResp.patientInfo.class);
                        patientInfoList.add(patientInfo);
                    }
                }
                setResult(patientInfoList);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }


    @DAOMethod(sql = " from FollowUpRecords where isNotify = 1 and notifyStatus = 0 and followUpStatus = 0 and planNotifyDate=:planNotifyDate")
    public abstract List<FollowUpRecords> queryNoticeRecords(@DAOParam("planNotifyDate") Date planNotifyDate);

    @DAOMethod(sql = " from FollowUpRecords where isNotify = 1 and notifyStatus = 1 and followUpStatus = 0 and planNotifyDate=:planNotifyDate")
    public abstract List<FollowUpRecords> queryNoticeRecordsYesterday(@DAOParam("planNotifyDate") Date planNotifyDate);


    @DAOMethod(sql = " from FollowUpRecords where recordId =:recordId  ")
    public abstract FollowUpRecords getFollowUpDetailById(@DAOParam("recordId") Long recordId);


    @DAOMethod(sql = " update FollowUpRecords set isRead=1  where recordId =:recordId  ")
    public abstract void updateFollowUpPlanReadById(@DAOParam("recordId") Long recordId);

    public Integer stopFollowUpPlanByrecordIds(final List<Long> medicalTeamIds, final StopExecutionListRequest request) {
        HibernateStatelessResultAction<Integer> action =
                new AbstractHibernateStatelessResultAction<Integer>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append("update follow_up_records  set follow_up_status =9, " +
                                " stop_id='" + request.getOperatorId() + "', stop_time=now(),gmt_modified=now() " +
                                "  where  record_id in (:recordIdArr) " +
                                " and medical_team_id in (:docTeamIdsarr) ");

                        String[] recordIdArr = request.getRecordIdArr().split(",");

                        Query query = ss.createSQLQuery(sqlBuilder.toString());
                        query.setParameterList("recordIdArr", recordIdArr);
                        query.setParameterList("docTeamIdsarr", medicalTeamIds);

                        int executeUpdate = query.executeUpdate();
                        setResult(executeUpdate);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    @DAOMethod(sql = " update FollowUpRecords set followUpStatus=:followUpStatus  where recordId =:recordId ")
    public abstract void updateFollowUpStastusById(@DAOParam("followUpStatus") Byte followUpStatus, @DAOParam("recordId") Long recordId);

    @DAOMethod(sql = " update FollowUpRecords set abnormalReadFlag='1' where recordId =:recordId ")
    public abstract void updateAbnormalReadFlag(@DAOParam("recordId") Long recordId);

    @DAOMethod(sql = " update FollowUpRecords set followUpStatus=:followUpStatus  where recordId in (:recordIdList) ")
    public abstract void updateFollowUpStastusByIds(@DAOParam("recordIdList") List<Long> recordIdList, @DAOParam("followUpStatus") Byte followUpStatus);


    /**
     * 修改随访计划的结果为失败
     *
     * @param resultCode 结果
     * @param recordId   随访记录id
     */
    public void updateResultCode(final Byte resultCode, final Long recordId) {
        HibernateStatelessResultAction<Void> action = new AbstractHibernateStatelessResultAction<Void>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" update follow_up_records set result_code=:resultCode where record_id=:recordId ");
                Query query = ss.createSQLQuery(sql.toString());
                query.setParameter("resultCode", resultCode);
                query.setParameter("recordId", recordId);
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                query.executeUpdate();
            }
        };
        HibernateSessionTemplate.instance().execute(action);
    }

    /**
     * 执行随访计划
     *
     * @param followUpRecords 随访计划结果信息
     */
    public void updateFollowUpRecordsResult(FollowUpRecords followUpRecords) {
        HibernateStatelessResultAction<Void> action = new AbstractHibernateStatelessResultAction<Void>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" update follow_up_records set result_code=:resultCode,follow_up_status=:followUpStatus,execute_id=:executeId" +
                        ",execute_name=:executeName,execute_time=:executeTime,gmt_modified=:gmtModified where record_id=:recordId ");
                Query query = ss.createSQLQuery(sql.toString());
                query.setParameter("recordId", followUpRecords.getRecordId());
                query.setParameter("resultCode", followUpRecords.getResultCode());
                query.setParameter("followUpStatus", followUpRecords.getFollowUpStatus());
                query.setParameter("executeId", followUpRecords.getExecuteId());
                query.setParameter("executeName", followUpRecords.getExecuteName());
                query.setParameter("executeTime", followUpRecords.getExecuteTime());
                query.setParameter("gmtModified", followUpRecords.getGmtModified());
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                query.executeUpdate();
            }
        };
        HibernateSessionTemplate.instance().execute(action);
    }

    /**
     * 根据病人id和病种id获取随访记录信息
     *
     * @param qo
     * @return
     */
    public List<FollowUpRecordsResponse> queryFollowUpRecordsInfoList(GroupChronicRecordQo qo) {
        HibernateStatelessResultAction<List<FollowUpRecordsResponse>> action = new AbstractHibernateStatelessResultAction<List<FollowUpRecordsResponse>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuilder sqlBuilder = new StringBuilder();
                sqlBuilder.append("select record_id as recordId,route_detail_type as routeDetailType,result_code as resultCode,follow_up_status as followUpStatus,answer_abnormal as abnormalFlag,abnormal_read_flag as abnormalReadFlag,gmt_create as gmtCreate,gmt_modified as gmtModified FROM follow_up_records WHERE patient_id =:patientId and disease_type_id =:diseaseTypeId ");
                sqlBuilder.append(" and create_time>=:beginTime and create_time <=:endTime and follow_up_status!= 9 order by create_time ");
                Query query = ss.createSQLQuery(sqlBuilder.toString());
                query.setParameter("patientId", qo.getPatientId());
                query.setParameter("diseaseTypeId", qo.getDiseaseTypeId());
                if (CommonUtils.isNotEmpty(qo.getBeginTime())){
                    query.setParameter("beginTime", qo.getBeginTime()+" 00:00:00");
                }
                if (CommonUtils.isNotEmpty(qo.getBeginTime())){
                    query.setParameter("endTime", qo.getEndTime()+" 23:59:59");
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                List<FollowUpRecordsResponse> voList = new ArrayList<>();
                if (CommonUtils.isNotEmpty(list)) {
                    for (Map map : list) {
                        voList.add(ConversionUtils.convert(map, FollowUpRecordsResponse.class));
                    }
                } else {
                    voList = new ArrayList<>();
                }
                setResult(voList);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    @DAOMethod(sql = " from FollowUpRecords where patientId =:patientId and followUpStatus in (0,2) ")
    public abstract List<FollowUpRecords> queryFollowUpRecordsByPersonId(@DAOParam("patientId") String patientId);


    public List<StatisticsRecordResponse> statisticsRecord(final StatisticsRecordRequest statisticsRecordRequest) {

        HibernateStatelessResultAction<List<StatisticsRecordResponse>> action =
                new AbstractHibernateStatelessResultAction<List<StatisticsRecordResponse>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append(" SELECT organization_name AS organizationName, fd.department_name AS departmentName, " +
                                "ftc.chronic_disease_name AS chronicDiseaseName, " +
                                "ftc.chronic_disease_id AS chronicDiseaseId, " +
                                "COUNT(ftc.team_chronic_group_id) AS historyNumber,");

                        if (CommonUtils.isNotEmpty(statisticsRecordRequest.getStartTime()) &&
                                CommonUtils.isNotEmpty(statisticsRecordRequest.getEndTime())) {
                            sqlBuilder.append(" sum(case when ftc.apply_time >= :startTime AND ftc.apply_time <= :endTime then 1 else 0 end) AS newNumber, ");
                        } else {
                            sqlBuilder.append(" COUNT(ftc.team_chronic_group_id) AS newNumber, ");

                        }
                        sqlBuilder.append("sum(case when ftc.out_person_id is NULL then 1 else 0 end) AS currentNumber " +
                                "FROM fus_team_chronic_group_record ftc,fus_disease_species_record  fd ");
                        sqlBuilder.append("WHERE ftc.chronic_disease_id = fd.disease_type_id ");
                        if (null != statisticsRecordRequest.getOrganizationId()) {
                            sqlBuilder.append("and ftc.organization_id= :organization_id ");
                        }
                        if (CommonUtils.isNotEmpty(statisticsRecordRequest.getDepartmentCode())) {
                            sqlBuilder.append("and ftc.department_id= :department_id ");
                        }
                        if (CommonUtils.isNotEmpty(statisticsRecordRequest.getChronicDiseaseId())) {
                            sqlBuilder.append("and ftc.chronic_disease_id= :chronic_disease_id ");
                        }


                        sqlBuilder.append("GROUP BY ftc.department_id, ftc.chronic_disease_id ,organization_name ,fd.department_name,ftc.chronic_disease_name");

                        Query query = ss.createSQLQuery(sqlBuilder.toString());

                        if (null != statisticsRecordRequest.getOrganizationId()) {
                            query.setParameter("organization_id", statisticsRecordRequest.getOrganizationId());
                        }
                        if (CommonUtils.isNotEmpty(statisticsRecordRequest.getDepartmentCode())) {
                            query.setParameter("department_id", statisticsRecordRequest.getDepartmentCode());
                        }
                        if (CommonUtils.isNotEmpty(statisticsRecordRequest.getChronicDiseaseId())) {
                            query.setParameter("chronic_disease_id", statisticsRecordRequest.getChronicDiseaseId());
                        }
                        if (CommonUtils.isNotEmpty(statisticsRecordRequest.getStartTime()) &&
                                CommonUtils.isNotEmpty(statisticsRecordRequest.getEndTime())) {
                            query.setParameter("startTime", statisticsRecordRequest.getStartTime());
                            query.setParameter("endTime", statisticsRecordRequest.getEndTime());
                        }
                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

                        List<Map> list = query.list();
                        List<StatisticsRecordResponse> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, StatisticsRecordResponse.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<StatisticsRecord> statisticsFollowRecord(final StatisticsRecordRequest statisticsRecordRequest) {
        HibernateStatelessResultAction<List<StatisticsRecord>> action =
                new AbstractHibernateStatelessResultAction<List<StatisticsRecord>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append(" SELECT fur.disease_type_id AS diseaseTypeId, COUNT(1) AS historyFollowUpNumber,sum(case when fur.result_code = 1 then 1 else 0 end) AS sucFollowUpNumber, concat(left(sum(case when fur.result_code = 1 then 1 else 0 end)/COUNT(1) *100,5),'%') as historyFollowUpRate " +
                                "FROM follow_up_records fur " +
                                "WHERE fur.follow_up_status = 1 ");
                        if (null != statisticsRecordRequest.getOrganizationId()) {
                            sqlBuilder.append("and fur.organization_id= :organization_id ");
                        }
                        if (CommonUtils.isNotEmpty(statisticsRecordRequest.getDepartmentCode())) {
                            sqlBuilder.append("and fur.department_id= :department_id ");
                        }
                        if (CommonUtils.isNotEmpty(statisticsRecordRequest.getChronicDiseaseId())) {
                            sqlBuilder.append("and fur.disease_type_id= :chronic_disease_id ");
                        }

                        sqlBuilder.append("GROUP BY fur.department_id, fur.disease_type_id ");

                        Query query = ss.createSQLQuery(sqlBuilder.toString());

                        if (null != statisticsRecordRequest.getOrganizationId()) {
                            query.setParameter("organization_id", statisticsRecordRequest.getOrganizationId());
                        }
                        if (CommonUtils.isNotEmpty(statisticsRecordRequest.getDepartmentCode())) {
                            query.setParameter("department_id", statisticsRecordRequest.getDepartmentCode());
                        }
                        if (CommonUtils.isNotEmpty(statisticsRecordRequest.getChronicDiseaseId())) {
                            query.setParameter("chronic_disease_id", statisticsRecordRequest.getChronicDiseaseId());
                        }

                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

                        List<Map> list = query.list();
                        List<StatisticsRecord> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, StatisticsRecord.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<WorkloadStatisticsResp> workloadStatisticsRespList(WorkloadStatisticsReq workloadStatisticsReq) {
        HibernateStatelessResultAction<List<WorkloadStatisticsResp>> action =
                new AbstractHibernateStatelessResultAction<List<WorkloadStatisticsResp>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append("SELECT  a.medical_team_id,a.medical_team_name teamName,a.doctor_id docCode,a.doc_id,a.doctor_name docName, " +
                                "a.num followUpNum, b.num sucFollowUpNum ,c.num beOverdueNum,c.num/a.num*100 beOverdueRate, d.num followUpVisitNum,e.num enterGroupNum FROM  ")
                                .append("(SELECT  medical_team_id,medical_team_name,doctor_id,doc_id,doctor_name,COUNT(record_id) num FROM follow_up_records WHERE 1=1 ");
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and plan_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and plan_time<= :endDate ");
                        }
                        if (null != workloadStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }
                        sqlBuilder.append(" GROUP BY  medical_team_id,doctor_id,medical_team_name,doc_id,doctor_name) a  ")
                                .append("LEFT JOIN (SELECT medical_team_id,doctor_id,COUNT(record_id) num FROM follow_up_records  WHERE  follow_up_status=1 AND result_code=1  ");
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and plan_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and plan_time<= :endDate ");
                        }
                        if (null != workloadStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }
                        sqlBuilder.append("GROUP BY  medical_team_id,doctor_id) b ON a.medical_team_id=b.medical_team_id AND a.doctor_id=b.doctor_id ")
                                .append("LEFT JOIN (SELECT medical_team_id,doctor_id,COUNT(record_id) num FROM follow_up_records WHERE  follow_up_status=0 AND plan_time<NOW()  ");
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and plan_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and plan_time<= :endDate ");
                        }
                        if (null != workloadStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }
                        sqlBuilder.append("GROUP BY  medical_team_id,doctor_id) c ON a.medical_team_id=c.medical_team_id AND a.doctor_id=c.doctor_id ")
                                .append("LEFT JOIN (SELECT medical_team_id,doctor_id,COUNT(record_id) num FROM follow_up_records  WHERE  route_detail_type=1 AND follow_up_status=1 AND result_code=1  ");
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and plan_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and plan_time<= :endDate ");
                        }
                        if (null != workloadStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }
                        sqlBuilder.append("GROUP BY  medical_team_id,doctor_id) d ON a.medical_team_id=d.medical_team_id AND a.doctor_id=d.doctor_id ")
                                .append("LEFT JOIN (SELECT medical_team_id,doctor_id,COUNT(team_chronic_group_id ) num FROM fus_team_chronic_group_record WHERE  apply_type=1  ");
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and apply_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and apply_time<= :endDate ");
                        }
                        if (null != workloadStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }
                        sqlBuilder.append("GROUP BY  medical_team_id,doctor_id) e ON a.medical_team_id=e.medical_team_id AND a.doctor_id=e.doctor_id   WHERE 1=1 ");
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getMedicalTeamId())) {
                            sqlBuilder.append(" and medical_team_id= :medicalTeamId ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocCode())) {
                            sqlBuilder.append(" and docCode= :docCode ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocName())) {
                            sqlBuilder.append(" and docName= :docName ");
                        }
                        sqlBuilder.append(" limit ").append(workloadStatisticsReq.getPageNo()).append(",").append(workloadStatisticsReq.getPageSize());
                        Query query = ss.createSQLQuery(sqlBuilder.toString());

                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getMedicalTeamId())) {
                            query.setParameter("medicalTeamId", workloadStatisticsReq.getMedicalTeamId());
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocCode())) {
                            query.setParameter("docCode", workloadStatisticsReq.getDocCode());
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocName())) {
                            query.setParameter("docName", workloadStatisticsReq.getDocName());
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getStartDate())) {
                            query.setParameter("startDate", workloadStatisticsReq.getStartDate());
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getEndDate())) {
                            query.setParameter("endDate", workloadStatisticsReq.getEndDate());
                        }
                        if (null != workloadStatisticsReq.getOrganizationId()) {
                            query.setParameter("organizationId", workloadStatisticsReq.getOrganizationId());
                        }
                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

                        List<Map> list = query.list();
                        List<WorkloadStatisticsResp> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, WorkloadStatisticsResp.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<DiseaseFollowUpStatisticsResp> diseaseFollowUpStatistics(DiseaseFollowUpStatisticsReq diseaseFollowUpStatisticsReq) {
        HibernateStatelessResultAction<List<DiseaseFollowUpStatisticsResp>> action =
                new AbstractHibernateStatelessResultAction<List<DiseaseFollowUpStatisticsResp>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append("SELECT a.organization_id,a.organization_name orgName,a.department_id,a.department_name deptName,a.chronic_disease_id,a.chronic_disease_name chronicDiseaseName,")
                                .append("a.num patientsNum,b.num inGroupNum,c.num sucFollowUpNum,d.num/e.num*100 followUpRate, f.num lostVisitNum FROM  ")
                                .append("(SELECT organization_id,organization_name,department_id,department_name,chronic_disease_id,chronic_disease_name,COUNT(DISTINCT(person_id)) num FROM fus_team_chronic_group_record where 1=1 and apply_status = 1 ");
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and apply_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and apply_time<= :endDate ");
                        }
                        if (null != diseaseFollowUpStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }
                        sqlBuilder.append(" GROUP BY  chronic_disease_id,organization_id,department_id)a ");
                        sqlBuilder.append("LEFT JOIN (SELECT chronic_disease_id,COUNT(DISTINCT(person_id)) num FROM fus_team_chronic_group_record WHERE  apply_status=1  ");
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and apply_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and apply_time<= :endDate ");
                        }
                        if (null != diseaseFollowUpStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }
                        sqlBuilder.append("GROUP BY  chronic_disease_id) b ON a.chronic_disease_id=b.chronic_disease_id ")
                                .append("LEFT JOIN (SELECT disease_type_id,COUNT(record_id) num FROM follow_up_records   WHERE follow_up_status=1 AND result_code=1   ");
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and plan_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and plan_time<= :endDate ");
                        }
                        if (null != diseaseFollowUpStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }
                        sqlBuilder.append("GROUP BY disease_type_id) c ON a.chronic_disease_id=c.disease_type_id ")
                                .append("LEFT JOIN (SELECT disease_type_id,COUNT(record_id) num FROM follow_up_records   WHERE route_detail_type=1 AND follow_up_status=1 AND result_code=1  ");
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and plan_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and plan_time<= :endDate ");
                        }
                        if (null != diseaseFollowUpStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }
                        sqlBuilder.append("GROUP BY disease_type_id) d ON a.chronic_disease_id=d.disease_type_id  ")
                                .append("LEFT JOIN (SELECT disease_type_id,COUNT(record_id) num FROM follow_up_records   WHERE route_detail_type=1  ");
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and plan_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and plan_time<= :endDate ");
                        }
                        if (null != diseaseFollowUpStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }
                        sqlBuilder.append("GROUP BY disease_type_id) e ON a.chronic_disease_id=e.disease_type_id ")
                                .append("LEFT JOIN (SELECT disease_type_id,COUNT(record_id) num FROM follow_up_records  WHERE  follow_up_status=1 AND result_code=0 ");
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and plan_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and plan_time<= :endDate ");
                        }
                        if (null != diseaseFollowUpStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }
                        sqlBuilder.append("GROUP BY disease_type_id) f ON a.chronic_disease_id=f.disease_type_id  WHERE 1=1 ");
                        if (null != diseaseFollowUpStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id= :organizationId ");
                        }
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getDeptId())) {
                            sqlBuilder.append(" and department_id= :deptId ");
                        }
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getChronicDiseaseId())) {
                            sqlBuilder.append(" and chronic_disease_id= :chronicDiseaseId ");
                        }
                        sqlBuilder.append(" limit ").append(diseaseFollowUpStatisticsReq.getPageNo()).append(",").append(diseaseFollowUpStatisticsReq.getPageSize());
                        Query query = ss.createSQLQuery(sqlBuilder.toString());

                        if (null != diseaseFollowUpStatisticsReq.getOrganizationId()) {
                            query.setParameter("organizationId", diseaseFollowUpStatisticsReq.getOrganizationId());
                        }
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getDeptId())) {
                            query.setParameter("deptId", diseaseFollowUpStatisticsReq.getDeptId());
                        }
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getChronicDiseaseId())) {
                            query.setParameter("chronicDiseaseId", diseaseFollowUpStatisticsReq.getChronicDiseaseId());
                        }
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getStartDate())) {
                            query.setParameter("startDate", diseaseFollowUpStatisticsReq.getStartDate());
                        }
                        if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getEndDate())) {
                            query.setParameter("endDate", diseaseFollowUpStatisticsReq.getEndDate());
                        }

                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

                        List<Map> list = query.list();
                        List<DiseaseFollowUpStatisticsResp> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, DiseaseFollowUpStatisticsResp.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }


    public BigInteger getCountByFollowUpRecords(final DiseaseFollowUpStatisticsReq diseaseFollowUpStatisticsReq) {
        HibernateStatelessResultAction<BigInteger> action = new AbstractHibernateStatelessResultAction<BigInteger>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuilder sql = new StringBuilder();
                sql.append("SELECT COUNT(DISTINCT(disease_type_id)) FROM follow_up_records where 1=1 ");
                if (null != diseaseFollowUpStatisticsReq.getOrganizationId()) {
                    sql.append(" and organization_id= :organizationId ");
                }
                if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getDeptId())) {
                    sql.append(" and department_id= :deptId ");
                }
                if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getChronicDiseaseId())) {
                    sql.append(" and disease_type_id= :chronicDiseaseId ");
                }
                if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getStartDate())) {
                    sql.append(" and plan_time>= :startDate ");
                }
                if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getEndDate())) {
                    sql.append(" and plan_time<= :endDate ");
                }
                Query query = ss.createSQLQuery(sql.toString());

                if (null != diseaseFollowUpStatisticsReq.getOrganizationId()) {
                    query.setParameter("organizationId", diseaseFollowUpStatisticsReq.getOrganizationId());
                }
                if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getDeptId())) {
                    query.setParameter("deptId", diseaseFollowUpStatisticsReq.getDeptId());
                }
                if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getChronicDiseaseId())) {
                    query.setParameter("chronicDiseaseId", diseaseFollowUpStatisticsReq.getChronicDiseaseId());
                }
                if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getStartDate())) {
                    query.setParameter("startDate", diseaseFollowUpStatisticsReq.getStartDate());
                }
                if (CommonUtils.isNotEmpty(diseaseFollowUpStatisticsReq.getEndDate())) {
                    query.setParameter("endDate", diseaseFollowUpStatisticsReq.getEndDate());
                }
                List list = query.list();
                if (list.size() == 1) {
                    Object o = list.get(0);
                    BigInteger count = (BigInteger) o;
                    setResult(count);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }


    public WorkloadStatisticsResp workloadStatisticsTotal(WorkloadStatisticsReq workloadStatisticsReq) {
        HibernateStatelessResultAction<WorkloadStatisticsResp> action =
                new AbstractHibernateStatelessResultAction<WorkloadStatisticsResp>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append("SELECT  '总计' teamName, '000' docCode,");
                        sqlBuilder.append("a.num followUpNum, b.num sucFollowUpNum ,c.num beOverdueNum,c.num/a.num*100 beOverdueRate, d.num followUpVisitNum,e.num enterGroupNum FROM  ");
                        sqlBuilder.append("(SELECT 1 mlink,COUNT(record_id) num FROM follow_up_records where 1=1  ");
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getMedicalTeamId())) {
                            sqlBuilder.append(" and medical_team_id= :medicalTeamId ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocCode())) {
                            sqlBuilder.append(" and doctor_id= :docCode ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocName())) {
                            sqlBuilder.append(" and doctor_name= :docName ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and plan_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and plan_time<= :endDate ");
                        }
                        if (null != workloadStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }

                        sqlBuilder.append(" ) a  LEFT JOIN  ");
                        sqlBuilder.append("(SELECT 1 mlink,COUNT(record_id) num FROM follow_up_records  WHERE  follow_up_status=1 AND result_code=1 ");
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getMedicalTeamId())) {
                            sqlBuilder.append(" and medical_team_id= :medicalTeamId ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocCode())) {
                            sqlBuilder.append(" and doctor_id= :docCode ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocName())) {
                            sqlBuilder.append(" and doctor_name= :docName ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and plan_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and plan_time<= :endDate ");
                        }
                        if (null != workloadStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }

                        sqlBuilder.append(") b ON a.mlink=b.mlink  LEFT JOIN  ");
                        sqlBuilder.append("(SELECT 1 mlink,COUNT(record_id) num FROM follow_up_records  WHERE  follow_up_status=0 AND plan_time<NOW() ");
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getMedicalTeamId())) {
                            sqlBuilder.append(" and medical_team_id= :medicalTeamId ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocCode())) {
                            sqlBuilder.append(" and doctor_id= :docCode ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocName())) {
                            sqlBuilder.append(" and doctor_name= :docName ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and plan_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and plan_time<= :endDate ");
                        }
                        if (null != workloadStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }

                        sqlBuilder.append(") c ON a.mlink=c.mlink  LEFT JOIN  ");
                        sqlBuilder.append("(SELECT 1 mlink,COUNT(record_id) num FROM follow_up_records  WHERE  route_detail_type=1 AND follow_up_status=1 AND result_code=1 ");
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getMedicalTeamId())) {
                            sqlBuilder.append(" and medical_team_id= :medicalTeamId ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocCode())) {
                            sqlBuilder.append(" and doctor_id= :docCode ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocName())) {
                            sqlBuilder.append(" and doctor_name= :docName ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and plan_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and plan_time<= :endDate ");
                        }
                        if (null != workloadStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }

                        sqlBuilder.append(") d ON a.mlink=d.mlink  LEFT JOIN  ");
                        sqlBuilder.append("(SELECT 1 mlink,COUNT(team_chronic_group_id ) num FROM fus_team_chronic_group_record WHERE  apply_type=1 ");
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getMedicalTeamId())) {
                            sqlBuilder.append(" and medical_team_id= :medicalTeamId ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocCode())) {
                            sqlBuilder.append(" and doctor_id= :docCode ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocName())) {
                            sqlBuilder.append(" and doctor_name= :docName ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getStartDate())) {
                            sqlBuilder.append(" and apply_time>= :startDate ");
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getEndDate())) {
                            sqlBuilder.append(" and apply_time<= :endDate ");
                        }
                        if (null != workloadStatisticsReq.getOrganizationId()) {
                            sqlBuilder.append(" and organization_id=:organizationId ");
                        }

                        sqlBuilder.append(") e ON a.mlink=e.mlink");

                        Query query = ss.createSQLQuery(sqlBuilder.toString());

                        if (null != workloadStatisticsReq.getOrganizationId()) {
                            query.setParameter("organizationId", workloadStatisticsReq.getOrganizationId());
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getMedicalTeamId())) {
                            query.setParameter("medicalTeamId", workloadStatisticsReq.getMedicalTeamId());
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocCode())) {
                            query.setParameter("docCode", workloadStatisticsReq.getDocCode());
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocName())) {
                            query.setParameter("docName", workloadStatisticsReq.getDocName());
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getStartDate())) {
                            query.setParameter("startDate", workloadStatisticsReq.getStartDate());
                        }
                        if (CommonUtils.isNotEmpty(workloadStatisticsReq.getEndDate())) {
                            query.setParameter("endDate", workloadStatisticsReq.getEndDate());
                        }
                        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

                        List<Map> list = query.list();
                        List<WorkloadStatisticsResp> voList = new ArrayList<>();
                        if (CommonUtils.isNotEmpty(list)) {
                            for (Map map : list) {
                                voList.add(ConversionUtils.convert(map, WorkloadStatisticsResp.class));
                            }
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList.get(0));
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public BigInteger getCountByFollowUpRecordsForWorkloadStatistics(final WorkloadStatisticsReq workloadStatisticsReq) {
        HibernateStatelessResultAction<BigInteger> action = new AbstractHibernateStatelessResultAction<BigInteger>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuilder sql = new StringBuilder();
                sql.append("SELECT COUNT(DISTINCT(CONCAT(medical_team_id,doctor_id))) FROM follow_up_records  where 1=1 ");
                if (CommonUtils.isNotEmpty(workloadStatisticsReq.getMedicalTeamId())) {
                    sql.append(" and medical_team_id= :medicalTeamId ");
                }
                if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocCode())) {
                    sql.append(" and doctor_id= :docCode ");
                }
                if (null != workloadStatisticsReq.getOrganizationId()) {
                    sql.append(" and organization_id=:organizationId ");
                }
                if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocName())) {
                    sql.append(" and doctor_name= :docName ");
                }
                if (CommonUtils.isNotEmpty(workloadStatisticsReq.getStartDate())) {
                    sql.append(" and plan_time>= :startDate ");
                }
                if (CommonUtils.isNotEmpty(workloadStatisticsReq.getEndDate())) {
                    sql.append(" and plan_time<= :endDate ");
                }
                Query query = ss.createSQLQuery(sql.toString());

                if (CommonUtils.isNotEmpty(workloadStatisticsReq.getMedicalTeamId())) {
                    query.setParameter("medicalTeamId", workloadStatisticsReq.getMedicalTeamId());
                }
                if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocCode())) {
                    query.setParameter("docCode", workloadStatisticsReq.getDocCode());
                }
                if (null != workloadStatisticsReq.getOrganizationId()) {
                    query.setParameter("organizationId", workloadStatisticsReq.getOrganizationId());
                }
                if (CommonUtils.isNotEmpty(workloadStatisticsReq.getDocName())) {
                    query.setParameter("docName", workloadStatisticsReq.getDocName());
                }
                if (CommonUtils.isNotEmpty(workloadStatisticsReq.getStartDate())) {
                    query.setParameter("startDate", workloadStatisticsReq.getStartDate());
                }
                if (CommonUtils.isNotEmpty(workloadStatisticsReq.getEndDate())) {
                    query.setParameter("endDate", workloadStatisticsReq.getEndDate());
                }
                List list = query.list();
                if (list.size() == 1) {
                    Object o = list.get(0);
                    BigInteger count = (BigInteger) o;
                    setResult(count);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }
}
